﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint.BusinessData.Administration;
using System.Data.SqlClient;
using Microsoft.SharePoint.Administration;
using System.Data;

namespace CNRSccp.Claims.DAO
{
    public class BaseDAO_mas
    {
        #region Field
        /// <summary>
        /// String de connexion à la base SQL
        /// </summary>
        protected string _connection;

        /// <summary>
        /// Table dédiée
        /// </summary>
        protected string _table;
        #endregion

        #region Constructors
        public BaseDAO_mas(string connection, string table)
        {
            if (string.IsNullOrEmpty(connection))
                throw new ArgumentNullException("connection or table");
            _connection = connection;
            _table = table;
        }
        #endregion

        #region Methods
        /// <summary>
        /// Récupère la liste d'item
        /// </summary>
        /// <returns></returns>
        public List<DataElement> GetItems(DAOParameter whereParameter)
        {
            List<DataElement> items = null;
            try
            {
                using (SqlConnection connexion = new SqlConnection())
                {
                    connexion.ConnectionString = _connection;
                    connexion.Open();

                    SqlCommand cmd = connexion.CreateCommand();
                    cmd.CommandText = BuildGetCommand(cmd, whereParameter);
                    using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        items = new List<DataElement>();
                        while (dr.Read())
                            items.Add(WrapItem(dr));
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            return items;
        }

        /// <summary>
        /// Récupère la liste d'item depuis la table LDAP
        /// </summary>
        /// <returns></returns>
        public List<LdapDbConfigEntity> GetLdapItems(DAOParameter whereParameter)
        {
            List<LdapDbConfigEntity> items = null;
            LdapDbConfigEntity entity = null;

            try
            {
                using (SqlConnection connexion = new SqlConnection(_connection))
                {
                    connexion.Open();

                    SqlCommand cmd = connexion.CreateCommand();
                    cmd.CommandText = BuildGetCommand(cmd, whereParameter);
                    using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        items = new List<LdapDbConfigEntity>();
                        while (dr.Read())
                        {
                            entity = new LdapDbConfigEntity();
                            entity.DirectoryEntry = dr["DirectoryEntry"].ToString();
                            entity.ServerBind = bool.Parse(dr["ServerBind"].ToString());
                            entity.UserAccount = dr["UserAccount"].ToString();
                            entity.Password = dr["Password"].ToString();
                            entity.Query = dr["Query"].ToString();
                            entity.Attribut = dr["Attribut"].ToString();
                            entity.SSL = bool.Parse(dr["SSL"].ToString());
                            items.Add(entity);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return items;
        }

        /// <summary>
        /// Récupère la liste d'item depuis la Table CLAIM
        /// </summary>
        /// <returns></returns>
        public List<ClaimDbConfigEntity> GetClaimItems(DAOParameter whereParameter)
        {
            List<ClaimDbConfigEntity> items = null;
            ClaimDbConfigEntity entity = null;
            try
            {
                using (SqlConnection connexion = new SqlConnection(_connection))
                {
                    connexion.Open();

                    SqlCommand cmd = connexion.CreateCommand();
                    cmd.CommandText = BuildGetCommand(cmd, whereParameter);
                    using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        items = new List<ClaimDbConfigEntity>();
                        while (dr.Read())
                        {
                            entity = new ClaimDbConfigEntity();
                            entity.Server = dr["Server"].ToString();
                            entity.DatabaseName = dr["DatabaseName"].ToString();
                            entity.Login = dr["Login"].ToString();
                            entity.Password = dr["Password"].ToString();
                            entity.ConnectionString = string.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3};", entity.Server, entity.DatabaseName, entity.Login, entity.Password);
                            items.Add(entity);
                        }
                    }
                }
            }
            catch (Exception ex)
            {   
                throw new Exception(ex.Message);
            }
            return items;
        }

        /// <summary>
        /// Récupère la liste d'item depuis la table CLAIMSELEMENT
        /// </summary>
        /// <returns></returns>
        public List<ClaimsElement> GetClaimsElementItems(DAOParameter whereParameter)
        {
            List<ClaimsElement> items = null;
            ClaimsElement entity = null;
            try
            {
                using (SqlConnection connexion = new SqlConnection(_connection))
                {
                    connexion.Open();

                    SqlCommand cmd = connexion.CreateCommand();
                    cmd.CommandText = BuildGetCommand(cmd, whereParameter);
                    using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        items = new List<ClaimsElement>();
                        while (dr.Read())
                        {
                            entity = new ClaimsElement();
                            entity.Title = dr["Title"].ToString();
                            entity.Type = dr["Type"].ToString();
                            entity.ClaimKey = dr["ClaimKey"].ToString();
                            entity.ReferenceID = dr["ReferenceID"].ToString();
                            entity.DB = dr["DB"].ToString();
                            entity.FormatDisplay = dr["FormatDisplay"].ToString();
                            entity.Format = dr["Format"].ToString();
                            entity.Hidden = bool.Parse(dr["Hidden"].ToString());
                            items.Add(entity);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            
            return items;
        }
        #endregion

        #region Methods
        /// <summary>
        /// Méthode SQL Get Command
        /// </summary>
        /// <param name="command"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        private string BuildGetCommand(SqlCommand command, DAOParameter whereParameter)
        {
            StringBuilder sb = new StringBuilder();
            if (!string.IsNullOrEmpty(whereParameter.ParameterName))
            {
                whereParameter.Value = "%" + whereParameter.Value + "%";
                command.Parameters.AddWithValue(whereParameter.ParameterName, whereParameter.Value);
                sb.AppendFormat("SELECT {1} FROM [{0}] WHERE {1} LIKE {2}", _table, whereParameter.Column, whereParameter.ParameterName);
            }
            else
                sb.AppendFormat("SELECT {1} FROM [{0}]", _table, whereParameter.Columns);

            return sb.ToString();
        }

        /// <summary>
        /// Wrap un item en objet
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        private DataElement WrapItem(SqlDataReader data)
        {
            DataElement element = new DataElement();
            element.Value = data[0].ToString();
            return element;
        }
        #endregion


    }
}
